package manage.commons.utils;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import manage.commons.ExcelEntry;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;

public class ExcelUtils {

	// 前台导出excel定位字符串
	public static final String EXPORTPARAM = "excel963456159357=1";
	public static final int sbi = 'A' - 'a';

	/**
	 * 
	 * 方法说明：取得map中的值或bean对象中的值(一定要符合bean规则)
	 * 
	 * @return
	 * @throws NoSuchFieldException
	 * @throws SecurityException
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 * @throws NoSuchMethodException
	 * @throws InvocationTargetException
	 */
	public static Object getObjValue(Object item, String field)
			throws Exception {
		if (item instanceof Map) {
			return ((Map<?, ?>) item).get(field);
		} else {
			Class<? extends Object> c = item.getClass();
			char firstLetter = field.charAt(0);
			if ('a' <= firstLetter && firstLetter <= 'z') {
				field = ((char) (firstLetter + ExcelUtils.sbi))
						+ field.substring(1);
			}
			String methodName = "get" + field;
			Method m = c.getDeclaredMethod(methodName);
			return m.invoke(item);
		}
	}

	/**
	 * 
	 * @param dataList
	 * @param params
	 *            --保证有序，所以这样使用
	 * @return
	 */
	public static HSSFWorkbook generateExcel(List<? extends Object> dataList,
			List<ExcelEntry> params) {
		return generateExcel(dataList, params, null);
	}

	/**
	 * <p>
	 * 生成excel
	 * </p>
	 * 支持自定义表头(可合并单元格、自定义宽度)
	 * 
	 * @param dataList
	 * @param params
	 * @param mergeList
	 *            需要合并的单元格(int firstRow, int lastRow, int firstCol, int lastCol)
	 * @return
	 */
	public static HSSFWorkbook generateExcel(List<? extends Object> dataList,
			List<ExcelEntry> params, List<int[]> mergeList) {
		if (dataList == null || dataList.isEmpty() || params == null
				|| params.isEmpty()) {
			return null;
		}
		HSSFWorkbook wb = generateHead(params, mergeList);
		try {
			HSSFSheet sheet = wb.getSheetAt(0);
			HSSFCellStyle cellStyle = wb.createCellStyle();
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
			// 生成内容
			int cIndex = sheet.getLastRowNum() + 1;
			for (int i = 0; i < dataList.size(); i++) {
				Object item = dataList.get(i);
				HSSFRow bodyRow = sheet.createRow(cIndex++);
				int columnIndex = 0;
				for (int j = 0; j < params.size(); j++) {
					ExcelEntry param = params.get(j);
					String key = param.getKey();
					if (StringUtils.isEmpty(key))
						continue;
					Object value = getObjValue(item, key);
					if (value == null)
						value = "";
					HSSFCell cell = bodyRow.createCell(columnIndex++);
					cell.setCellStyle(cellStyle);
					if (value instanceof Number) {
						if (StringUtils.isNotBlank(param.getFormatPattern())) {
							NumberFormat nf = new DecimalFormat(
									param.getFormatPattern());
							value = nf.format(value);
							cell.setCellValue(new HSSFRichTextString(value
									.toString()));
						} else {
							cell.setCellValue(((Number) value).doubleValue());
						}
					} else if (value instanceof Date) {
						if (StringUtils.isNotBlank(param.getFormatPattern())) {
							cell.setCellValue(new HSSFRichTextString(DateUtil
									.date2String((Date) value,
											param.getFormatPattern())));
						} else {
							cell.setCellValue(new HSSFRichTextString(DateUtil
									.formateDateToStr((Date) value)));
						}
					} else if (value instanceof Calendar) {
						cell.setCellValue((Calendar) value);
					} else if (value instanceof Boolean) {
						String temp = (Boolean) value ? "是" : "否";
						cell.setCellValue(new HSSFRichTextString(temp));
					} else {
						cell.setCellValue(new HSSFRichTextString(value
								.toString()));
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("生成excel文件出错");
		}
		return wb;
	}

	/**
	 * 生成一个只有表头的workbook
	 * 
	 * @param params
	 *            表头对象集合
	 * @param mergeList
	 *            需要合并的单元格(int firstRow, int lastRow, int firstCol, int lastCol)
	 * @return
	 */
	public static HSSFWorkbook generateHead(List<ExcelEntry> params,
			List<int[]> mergeList) {
		if (params == null || params.isEmpty()) {
			return null;
		}
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("sheet");
		HSSFCellStyle headerStyle = wb.createCellStyle();
		headerStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);
		headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 标题垂直居中
		headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 标题水平居中
		headerStyle.setFillForegroundColor(new HSSFColor.LIGHT_BLUE()
				.getIndex());
		headerStyle.setFillBackgroundColor(new HSSFColor.CORNFLOWER_BLUE()
				.getIndex());
		HSSFFont bold = wb.createFont();
		bold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		bold.setColor(HSSFColor.WHITE.index);
		headerStyle.setFont(bold);
		if (mergeList != null && mergeList.size() > 0) {
			for (int i = 0; i < params.size(); i++) {
				ExcelEntry param = params.get(i);
				String title = param.getTitle();
				int rowIndex = param.getRowIndex();
				int columnIndex = param.getColumnIndex();
				HSSFRow titleRow = sheet.getRow(rowIndex);
				if (titleRow == null) {
					titleRow = sheet.createRow(rowIndex);
				}
				HSSFCell cell = titleRow.createCell(columnIndex);
				cell.setCellValue(new HSSFRichTextString(title));
				cell.setCellStyle(headerStyle);
				sheet.setColumnWidth(columnIndex, param.getWidth());
			}
			// 合并单元格
			for (int i = 0; i < mergeList.size(); i++) {
				int[] indexArray = mergeList.get(i);
				sheet.addMergedRegion(new CellRangeAddress(indexArray[0],
						indexArray[1], indexArray[2], indexArray[3]));
			}
		} else {
			HSSFRow titleRow = sheet.createRow(0);
			for (int i = 0; i < params.size(); i++) {
				ExcelEntry param = params.get(i);
				String title = param.getTitle();
				HSSFCell cell = titleRow.createCell(i);
				cell.setCellValue(new HSSFRichTextString(title));
				cell.setCellStyle(headerStyle);
				sheet.setColumnWidth(i, param.getWidth());
			}
		}
		return wb;
	}

}
